﻿using ASmile.CodeFactory.DBModel;
using ASmile.Com.Da;
using ASmile.Tools;
using ASmile.Extend;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace ASmile.CodeFactory.DaBLL.Builder
{
    abstract class IBuilder
    {
        public IBuilder(TConfig config)
        {
            Config = config;
            DaBLL = new DBMain(config.ConnString, config.DBType);
        }

        protected TConfig Config { get; }

        public DBMain DaBLL { get; }

        public void SetConfigRemark()
        {
            DaMain da = new DaMain();
            ConfigTableList = da.DB.Query<TTableRemark>(s => s.ConfigId == Config.Id).ToList(true);
            ConfigFiledList = da.DB.Query<TFieldRemark>(s => s.ConfigId == Config.Id).ToList(true);
        }

        //DaMain ConfigDa { get; } = new DaMain();

        //List<TTableRemark> _ConfigTableList;

        List<TTableRemark> ConfigTableList { set; get; }

        List<TFieldRemark> ConfigFiledList { set; get; }

        protected virtual DataTable GetSchema(string sort, string collectionName, params string[] restrictionValues)
        {
            var dbConnect = DaBLL.DbHelper.DBConnectionObj;
            try
            {
                dbConnect.Open();
                DataTable dt = dbConnect.GetSchema(collectionName, restrictionValues);
                for (int i = 0; i < restrictionValues?.Length; i++)
                {
                    if (restrictionValues[i].IsEmpty()) restrictionValues[i] = null;
                }
                dt.DefaultView.Sort = sort; //"Table_Type,Table_Name";
                var tableInfo = dt.DefaultView.ToTable();
                return tableInfo;

            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                dbConnect.Close();
            }
        }

        protected virtual DataTable GetTableSchema()
        {
            return GetSchema("Table_Name", "Tables");
        }

        protected virtual DataTable GetViewSchema()
        {
            return GetSchema("Table_Name", "Views");
        }

        public virtual List<M_Table> GetTables(bool onlyDb = false)
        {
            var rList = new List<M_Table>();
            var dt = GetTableSchema();
            List<TTableRemark> hideTables = null;
            if (!onlyDb)
            {
                hideTables = ConfigTableList?.FindAll(s => s.IsHide == true);
            }
            foreach (DataRow dr in dt.Rows)
            {
                var tbName = dr["Table_Name"].ToString();

                if (ConfigTableList?.Exists(s => s.TableName == tbName && s.IsHide == true) == true)
                {
                    continue;
                }

                var m = new M_Table
                {
                    TableName = tbName
                };
                if (hideTables != null && hideTables.Exists(s => s.TableName == m.TableName))
                {
                    continue;
                }
                var remarks = GetTableRemarks();
                if (remarks != null && remarks.TryGetValue(m.TableName, out string val))
                {
                    m.TableRemark = val;
                }
                rList.Add(m);
            }
            return rList;
        }

        public virtual List<M_View> GetViews()
        {
            var rList = new List<M_View>();
            var dt = GetViewSchema();
            foreach (DataRow dr in dt.Rows)
            {
                var tbName = dr["Table_Name"].ToString();

                rList.Add(new M_View
                {
                    TableName = tbName
                });
            }
            return rList;
        }

        protected virtual DbDataReader GetDataReader(string sql)
        {
            DbConnection conn = DaBLL.DbHelper.DBConnectionObj;
            using (DbCommand cmd = DaBLL.DbHelper.DbCommandObj)
            {
                cmd.Connection = conn;
                cmd.CommandText = sql;
                conn.Open();
                DbDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                return reader;
            }
        }

        protected virtual Dictionary<string, string> GetDbFieldRemarks(string tbName)
        {
            return null;
        }

        private Dictionary<string, string> GetFieldRemarks(string tbName, bool onlyConfig = false)
        {
            //var rList = ConfigDa.DB.Query<TFieldRemark>(s => s.ConfigId == Config.Id && s.TableName == tbName).ToList();
            var rList = ConfigFiledList?.FindAll(s => s.TableName == tbName);
            if (rList != null && rList.Count > 0)
            {
                var rDict = new Dictionary<string, string>();
                foreach (var item in rList)
                {
                    if (item.RemarkSelf.IsNotEmpty())
                    {
                        rDict[item.FieldName] = item.RemarkSelf;
                    }
                    else
                    {
                        rDict[item.FieldName] = item.RemarkSrc;
                    }
                }
                return rDict;
            }
            else if (!onlyConfig)
            {
                return GetDbFieldRemarks(tbName);
            }
            return null;
        }

        protected virtual Dictionary<string, string> GetDbTableRemarks()
        {
            return null;
        }

        private Dictionary<string, string> GetTableRemarks()
        {
            //var rList = ConfigDa.DB.Query<TTableRemark>(s => s.ConfigId == Config.Id && (s.TableType == "table" || s.TableType == "view")).ToList();

            var rList = ConfigTableList?.FindAll(s => s.TableType == EmTableType.Table.ToString() || s.TableType == EmTableType.View.ToString());
            if (rList != null && rList.Count > 0)
            {
                var rDict = new Dictionary<string, string>();
                foreach (var item in rList)
                {
                    if (item.RemarkSelf.IsNotEmpty())
                    {
                        rDict[item.TableName] = item.RemarkSelf;
                    }
                    else
                    {
                        rDict[item.TableName] = item.RemarkSrc;
                    }
                }
                return rDict;
            }
            else
            {
                return GetDbTableRemarks();
            }
        }

        protected virtual Dictionary<string, string> GetDbProcRemarks()
        {
            return null;
        }

        private Dictionary<string, string> GetProcRemarks()
        {
            //var rList = ConfigDa.DB.Query<TTableRemark>(s => s.ConfigId == Config.Id && (s.TableType == EmTableType.Proc.ToString())).ToList();
            var rList = ConfigTableList?.FindAll(s => s.TableType == EmTableType.Proc.ToString());
            if (rList != null && rList.Count > 0)
            {
                var rDict = new Dictionary<string, string>();
                foreach (var item in rList)
                {
                    if (item.RemarkSelf.IsNotEmpty())
                    {
                        rDict[item.TableName] = item.RemarkSelf;
                    }
                    else
                    {
                        rDict[item.TableName] = item.RemarkSrc;
                    }
                }
                return rDict;
            }
            else
            {
                return GetDbProcRemarks();
            }
        }

        protected virtual Dictionary<string, string> GetDbProcArgRemarks(string procName)
        {
            return null;
        }

        private Dictionary<string, string> GetProcArgRemarks(string procName)
        {
            var rDict = GetFieldRemarks(procName, true);
            if (rDict == null)
            {
                return GetDbProcArgRemarks(procName);
            }
            return rDict;
        }

        protected virtual string DbDataTypeToCSharp(string dbDataType)
        {
            string r;
            switch (dbDataType.ToLower())
            {
                case "tinyint":
                case "int":
                case "integer":
                    r = "int";
                    break;
                case "smallint":
                    r = "uint";
                    break;
                case "bigint":
                    r = "long";
                    break;
                case "boolean":
                case "bit":
                    r = "bool";
                    break;
                case "uniqueidentifier":
                    r = "Guid";
                    break;
                case "smalldatetime":
                case "datetime":
                case "date":
                    r = "DateTime";
                    break;
                case "decimal":
                case "money":
                case "numeric":
                case "smallmoney":
                    r = "decimal";
                    break;
                case "float":
                case "real":
                    r = "double";
                    break;
                case "varchar":
                case "nvarchar":
                case "char":
                case "text":
                case "":
                    r = "string";
                    break;
                default:  //未知的数据类型都是string
                    r = "string";
                    break;
            }
            return r;
        }

        protected virtual List<string> GetPrimarykeys(string tableName)
        {
            return new List<string>();
        }

        public virtual List<M_Column> GetTableColumns(string tableName, bool isDbRemark = false)
        {
            List<M_Column> rList = new List<M_Column>();

            var reader = GetDataReader($"select * from {tableName} where 1!=1");
            DataTable colInfo;
            using (reader)
            {
                colInfo = reader.GetSchemaTable();
            }
            if (colInfo == null || colInfo.Rows.Count == 0) return null;
            //是否没有主键
            bool hasPK = false;
            //bool isHide = false;
            for (int i = 0; i < colInfo.Rows.Count; i++)
            {                
                //if (colInfo.Columns.Contains("IsHidden"))
                //{
                //    isHide = colInfo.Rows[i]["IsHidden"] == DBNull.Value ? false : Convert.ToBoolean(colInfo.Rows[i]["IsHidden"]);
                //}
                //else
                //{
                //    isHide = false;
                //}
                //if (isHide) continue;
                var filedName = colInfo.Rows[i]["ColumnName"].ToString(); //列名
                if (ConfigFiledList?.Exists(s => s.FieldName == filedName && s.IsHide) == true)
                {
                    continue;
                }
                M_Column model = new M_Column();
                model.TableName = tableName;
                model.ColumnIndex = i;
                model.ColumnName = filedName; //列名
                                                                             //数据库中数据类型
                if (colInfo.Columns.Contains("DataTypeName"))
                {
                    model.DataTypeByDB = colInfo.Rows[i]["DataTypeName"].ToString();
                }
                model.DataTypeByCSharp = colInfo.Rows[i]["DataType"].ToString();
                model.ColumnLength = Convert.ToInt32(colInfo.Rows[i]["ColumnSize"]);
                model.ColumnIndex = Convert.ToInt32(colInfo.Rows[i]["ColumnOrdinal"]);
                Dictionary<string, string> remarks;
                if (isDbRemark)
                {
                    remarks = GetDbFieldRemarks(tableName);
                }
                else
                {
                    remarks = GetFieldRemarks(tableName);
                }
                if (remarks != null && remarks.TryGetValue(model.ColumnName, out string val))
                {
                    model.ColumnRemark = val;
                }
                //sqlite 数据库自增长列的元结构列为:IsAutoIncrement
                //sqlserver 为: IsIdentity
                string[] identityColumns = { "IsIdentity", "IsAutoIncrement" };
                foreach (string colName in identityColumns)
                {
                    if (colInfo.Columns.Contains(colName) && colInfo.Rows[i][colName] != DBNull.Value)
                    {
                        model.IsIdentity = Convert.ToBoolean(colInfo.Rows[i][colName]);
                        break;
                    }
                }

                model.IsNullAble = Convert.ToBoolean(colInfo.Rows[i]["AllowDBNull"]);
                model.IsPK = colInfo.Rows[i]["IsKey"] == DBNull.Value ? false : Convert.ToBoolean(colInfo.Rows[i]["IsKey"]);
                model.IsUse = true;
                if (model.IsPK) hasPK = true;
                rList.Add(model);
            }
            //没有主键从数据库中查询，sqlserver 2008 不能从SchemaTable 中获取主键
            if (!hasPK)
            {
                var pks = GetPrimarykeys(tableName);
                if (pks?.Count > 0)
                {
                    pks.ForEach((pkCol) =>
                    {
                        var col = rList.Find(s => s.ColumnName == pkCol);
                        col.IsPK = true;
                        hasPK = true;
                    });
                }
            }
            //如果还没有主键
            if (!hasPK)
            {
                //2014.8.21 如果没有设定主键,则把第一个子增长的字段设置为主键                    
                foreach (var item in rList)
                {
                    if (item.IsIdentity)
                    {
                        item.IsPK = true;
                        hasPK = true;
                        item.Remark = "主键?";
                        break;
                    }
                }
            }
            //如果还没有主键
            if (!hasPK)
            {
                //2014.8.21 如果没有设定主键,则把第一个不为空的字段设置为主键                    
                foreach (var item in rList)
                {
                    if (!item.IsNullAble)
                    {
                        item.IsPK = true;
                        hasPK = true;
                        item.Remark = "主键?";
                        break;
                    }
                }
            }
            //如果还没有主键
            if (!hasPK)
            {
                //2014.8.21 如果没有设定主键,则把第一个字段设置为主键                    
                foreach (var item in rList)
                {
                    item.IsPK = true;
                    hasPK = true;
                    item.Remark = "主键?";
                    break;
                }
            }
            return rList;

        }

        protected virtual DataTable GetProcSchema()
        {
            return GetSchema("Specific_Name", "Procedures", null, null, null, "Procedure");
        }

        public virtual List<M_Proc> GetProcs(bool isDbRemark = false)
        {
            List<M_Proc> rList = new List<M_Proc>();
            var dt = GetProcSchema();
            if (dt == null || dt.Rows.Count == 0) return null;
            foreach (DataRow dr in dt.Rows)
            {
                var procName = dr["Specific_Name"].ToString();
                if (ConfigTableList?.Exists(s => s.TableType == EmTableType.Proc.ToString() && s.TableName == procName && s.IsHide) == true)
                {
                    continue;
                }
                M_Proc model = new M_Proc();
                model.ProcName = procName;



                model.ProcType = dr["Routine_Type"].ToString();
                Dictionary<string, string> remarks;
                if (isDbRemark)
                {
                    remarks = GetDbProcRemarks();
                }
                else
                {
                    remarks = GetProcRemarks();
                }

                if (remarks != null && remarks.TryGetValue(model.ProcName, out string val))
                {
                    model.ProcCaption = val;
                }
                rList.Add(model);
            }
            return rList;
        }

        public virtual DataTable GetProcParamSchema(string procName)
        {
            return GetSchema("ORDINAL_POSITION", "ProcedureParameters", null, null, procName);
        }

        public virtual List<M_ProcParam> GetProcParam(string procName, bool isDbRemark = false)
        {
            List<M_ProcParam> rList = new List<M_ProcParam>();
            var dt = GetProcParamSchema(procName);
            foreach (DataRow dr in dt.Rows)
            {
                string dtProcName = dr["Specific_Name"].ToString();
                if (!dtProcName.StringEquals(procName))
                {
                    continue;
                }
                M_ProcParam model = new M_ProcParam();
                string paramName = dr["Parameter_Name"].ToString(); //参数名
                if (string.IsNullOrEmpty(paramName)) continue;
                model.ParamName = paramName.Remove(0, 1); //删除第一个 @ 符号

                if (ConfigFiledList?.Exists(s => s.TableName == procName && s.FieldName== model.ParamName && s.IsHide) == true)
                {
                    continue;
                }

                model.ParamDataType = dr["Data_Type"].ToString();
                model.CSharpDataType = DbDataTypeToCSharp(model.ParamDataType);
                if (dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"].ToString().Length > 0)
                {
                    model.ParamLength = Convert.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"]);
                }
                model.ParamOutType = dr["PARAMETER_MODE"].ToString();
                Dictionary<string, string> remarks;
                if (isDbRemark)
                {
                    remarks = GetDbProcArgRemarks(procName);
                }
                else
                {
                    remarks = GetProcArgRemarks(procName);
                }

                if (remarks != null && remarks.TryGetValue(model.ParamName, out string val))
                {
                    model.ParamCaption = val;
                }

                if (dt.Columns.Contains("ORDINAL_POSITION"))
                {
                    model.ParamIndex = Convert.ToInt32(dr["ORDINAL_POSITION"]);
                }

                rList.Add(model);
            }
            return rList;
        }

        public string GetTableCode(M_Table table, List<M_Column> colList)
        {
            StringPlus sp = new StringPlus();
            sp.AppendLine($"/*CodeBuilder 3.0.1 by {DateTime.Now:yyyy-MM-dd HH:mm} */");
            sp.AppendLine("using System;");
            sp.AppendLine("using ASmile.ORM.Entitys;");
            sp.AppendLine();
            sp.AppendLine("namespace {0}", Config.NameSpace);
            sp.AppendLine("{");
            sp.AppendSpaceLine(1, "/// <summary>");
            sp.AppendSpaceLine(1, $"/// {table.TableRemark}");
            sp.AppendSpaceLine(1, "/// </summary>");
            sp.AppendSpaceLine(1, $"public partial class {table.TableName} : EntityBase");
            sp.AppendSpaceLine(1, "{");
            StringPlus fieldNameSP = new StringPlus();
            fieldNameSP.AppendSpaceLine(2, "#region 字段名的定义");
            foreach (var col in colList)
            {
                fieldNameSP.AppendSpaceLine(2, "public const string CN{0} = \"{0}\";", col.ColumnName);
                var dataType = DbDataTypeToCSharp(col.DataTypeByDB);
                if (col.IsNullAble)
                {
                    var t = Type.GetType(col.DataTypeByCSharp);
                    if (t.IsValueType)
                    {
                        dataType += "?";
                    }
                }

                sp.AppendSpaceLine(2, $"private {dataType} _{col.ColumnName};");
                sp.AppendSpaceLine(2, "/// <summary>");
                sp.AppendSpaceLine(2, "/// {0}", col.ColumnRemark);
                sp.AppendSpaceLine(2, "/// </summary>");
                sp.AppendSpace(2, "[Entity(CN{0}, {1}", col.ColumnName, col.ColumnIndex);
                if (!col.IsNullAble)
                {
                    sp.Append(", true");
                }
                if (col.IsPK)
                {
                    sp.Append(", IsPrimaryKey = true");
                }
                if (col.IsIdentity)
                {
                    sp.Append(", IsIdentity = true");
                }
                sp.AppendLine(")]");
                sp.AppendSpaceLine(2, "public {0} {1}", dataType, col.ColumnName);
                sp.AppendSpaceLine(2, "{");
                sp.AppendSpaceLine(2 + 1, "get {{ return _{0}; }}", col.ColumnName);
                sp.AppendSpaceLine(2 + 1, "set");
                sp.AppendSpaceLine(2 + 1, "{");
                sp.AppendSpaceLine(2 + 1 + 1, "if (!OnPropertyChanged(CN{0}, _{0}, value)) return;", col.ColumnName);
                sp.AppendSpaceLine(2 + 1 + 1, "_{0} = value;", col.ColumnName);
                sp.AppendSpaceLine(2 + 1 + 1, "SetFieldChanged(CN{0}) ;", col.ColumnName);
                sp.AppendSpaceLine(2 + 1, "}");
                sp.AppendSpaceLine(2, "}");
                sp.AppendLine();
            }
            fieldNameSP.AppendSpaceLine(2, "#endregion");
            sp.AppendLine(fieldNameSP.ToString());
            sp.AppendSpaceLine(1, "}");
            sp.AppendLine("}");
            return sp.ToString();
        }

        public string GetTableCode(M_Table table)
        {
            if (table == null) return null;
            var colList = GetTableColumns(table.TableName);
            return GetTableCode(table, colList);
        }

        public string GetViewCode(M_View view, List<M_Column> colLists)
        {
            StringPlus sp = new StringPlus();
            sp.AppendLine($"/*CodeBuilder 3.0.1 by {DateTime.Now:yyyy-MM-dd HH:mm} */");
            sp.AppendLine("using System;");
            sp.AppendLine("using ASmile.ORM.Entitys;");            
            sp.AppendLine();
            sp.AppendLine("namespace {0}", Config.NameSpace);
            sp.AppendLine("{");
            sp.AppendSpaceLine(1, "/// <summary>");
            sp.AppendSpaceLine(1, $"/// {view.TableRemark}");
            sp.AppendSpaceLine(1, "/// </summary>");
            sp.AppendSpaceLine(1, $"public partial class {view.TableName}  : EntityViewBase");
            sp.AppendSpaceLine(1, "{");

            foreach (var col in colLists)
            {
                var dataType = DbDataTypeToCSharp(col.DataTypeByDB);
                if (col.IsNullAble)
                {
                    var t = Type.GetType(col.DataTypeByCSharp);
                    if (t.IsValueType)
                    {
                        dataType += "?";
                    }
                }
                sp.AppendSpaceLine(2, "/// <summary>");
                sp.AppendSpaceLine(2, "/// {0}", col.ColumnRemark);
                sp.AppendSpaceLine(2, "/// </summary>");
                sp.AppendSpaceLine(2, $"public {dataType} {col.ColumnName} {{ get; set; }}");
            }
            sp.AppendSpaceLine(1, "}");
            sp.AppendLine("}");
            return sp.ToString();
        }

        public string GetViewCode(M_View view)
        {
            if (view == null) return null;
            var colList = GetTableColumns(view.TableName);
            return GetViewCode(view, colList);
        }

        public string GetProcCode(M_Proc proc, List<M_ProcParam> paramList)
        {
            StringPlus sp = new StringPlus();
            sp.AppendLine("using System;");
            sp.AppendLine("using System.Data;");
            sp.AppendLine("using ASmile.ORM.Entitys;");
            sp.AppendLine();
            sp.AppendLine("namespace {0}", Config.NameSpace);
            sp.AppendLine("{");
            sp.AppendSpaceLine(1, "/// <summary>");
            sp.AppendSpaceLine(1, $"/// {proc.ProcCaption}");
            sp.AppendSpaceLine(1, "/// </summary>");
            sp.AppendSpaceLine(1, "public partial class {0} : ProcEntityBase", proc.ProcName);
            sp.AppendSpaceLine(1, "{");
            for (int i = 0; i < paramList.Count; i++)
            {
                var item = paramList[i];
                sp.AppendSpaceLine(2, "/// <summary>");
                sp.AppendSpaceLine(2, "/// {0}", item.ParamCaption);
                sp.AppendSpaceLine(2, "/// </summary>");
                if (item.ParamOutType.Contains("OUT"))
                {
                    sp.AppendSpaceLine(2, "[ProcParam(ParamDirection= ParameterDirection.Output)]");
                }
                sp.AppendSpace(2, "public {0} {1}", item.CSharpDataType, item.ParamName);
                sp.AppendLine(" { set; get; }");
                sp.AppendLine();
            }
            sp.AppendSpaceLine(1, "}");
            sp.AppendLine("}");
            return sp.ToString();
        }

        public string GetProcCode(M_Proc proc)
        {
            if (proc == null) return null;
            List<M_ProcParam> paramList = GetProcParam(proc.ProcName);
            return GetProcCode(proc, paramList);
        }
    }
}